''' 二次封装pymsql '''

import pymysql

config = {
    'host':"127.0.0.1",
    'port':3306,
    'user':"root",
    'passwd':"root",
    'database':"37games", #设置要操作的数据库
    "charset":"utf8", #设置字符集 支持中文
    #'unix_socket':"/tmp/mysql.sock",  #unix系统下的设置
    #'db':"mysql",
    #'db':"mysql",
}

class MySql():
    ''' 封装pymsql实现增删查改 '''

    def __init__(self,**kwargs):
        self.isLog = False #是否打印sql语句
        self.conn = pymysql.connect(**kwargs) #链接数据库
        self.cur = self.conn.cursor() #获取当前游标
    
    def execute(self,sql):
        ''' 执行一条sql语句 '''
        return self.cur.execute(sql)

    def use_db(self,dbName):
        return self.execute("use "+dbName)

    def insert(self,table,**kwargs):
        ''' 只能插入一条数据 '''
        fields = ""
        values = ""
        for k,v in kwargs.items():
            fields += k+","
            values += "'"+str(v)+"',"
        
        fields = fields[:-1]
        values = values[:-1]
        sql = "insert into "+table +" ("+fields+") values ("+values+")"
    
        return self.__insert(sql)

    def insertMultiple(self,table,fields,values):
        ''' 
            可以插入多条或一条数据 
            table:表名
            fields:字典名 list或者字符串
            values:对应要插入的数据 list(插入多条是二维数组)或者字符串
        '''
        if len(fields)==0 or len(values)==0:
            raise ValueError(" fields or values can't empty")

        fs = self.__fmtInsertVal(fields,False) if isinstance(fields,list) else fields
       
        if isinstance(values,list):            
            valStr=""
            if isinstance(values[0],list):
                values = [self.__fmtInsertVal(val) for val in values]                
                valStr = ",".join(values)                
            else:
                valStr = self.__fmtInsertVal(values)

        elif isinstance(values,str):
            valStr= values
        else:
            raise ValueError("values must be str or list")
        
        sql = "insert into "+table + " " + fs + " values "+valStr    

        '''   
        self.__logSql("insertMultiple sql:",sql)
        ret = self.cur.execute(sql)
        self.conn.commit()
        '''
        return self.__insert(sql)

    def delete(self,table,conditions):
        ''' 删除数据 table:表名;conditions:字符串或者dict '''
        _,cs = self.__fmtFieldCondition(None,conditions)
        sql = "delete from "+table + " where "+cs
        self.__logSql("delete sql:",sql)
        ret = self.cur.execute(sql)
        self.conn.commit()
        return ret

    def selectOne(self,table,fields,conditions):
        self.__select(table,fields,conditions)
        return self.cur.fetchone()
    
    def selectAll(self,table,fields,conditions):
        self.__select(table,fields,conditions)
        return self.cur.fetchall()
    
    def update(self,table,valueKw,conditions):
        '''
            更新数据库
            valueKw 要更新的字段键值对字典或者直接字符串
            conditions 查询条件 字典或者字符串
        '''
        _,kws = self.__fmtFieldCondition(None,valueKw)
        _,cds = self.__fmtFieldCondition(None,conditions)
        sql = "update "+table+" set "+kws+" where "+cds
        self.__logSql("update sql:",sql)
        ret = self.cur.execute(sql)
        self.conn.commit()
        return ret

    def __fmtInsertVal(self,values,isQuo=True):        
        values = [ "'"+str(val)+"'" for val in values]
        valStr = ",".join(values)        
        valStr = "("+valStr+")"
        if  not isQuo:
            valStr = valStr.replace("'","")
       
        return valStr

    def __insert(self,sql):
        self.__logSql("__insert:",sql)
        ret = self.cur.execute(sql)
        self.conn.commit()
        return ret

    def __select(self,table,fields,conditions):
        fs,cs = self.__fmtFieldCondition(fields,conditions)
        sql = "select "+fs+" from "+table + " where "+cs
        self.__logSql("__select sql:",sql)
        self.cur.execute(sql)

    def __fmtFieldCondition(self,fields,conditions):
        '''
        格式化字段和条件
        '''
        fieldStr = ""
        condiStr = ""
        if fields:
            if isinstance(fields,list):
                fieldStr = ",".join(fields)
            elif isinstance(fields,str):
                fieldStr = fields
        if conditions:
            if isinstance(conditions,dict):
                for k,v in conditions.items():
                    condiStr+=str(k) + "='"+str(v)+"',"
                condiStr = condiStr[:-1]
            if isinstance(conditions,str):
                condiStr = conditions
                        
        return fieldStr,condiStr

    def __logSql(self,*arg):
        ''' 打印sql日志 '''
        if self.isLog:
            print(*arg)

    def close(self):
        self.conn.close()
        self.cur.close()